<?php

/**
 * 余额，积分，红包对账
 * ============================================================================
 */

define('IN_ECS', true);

require(dirname(__FILE__) . '/includes/init.php');
require_once(ROOT_PATH . 'includes/lib_order.php');
require_once(ROOT_PATH . 'includes/lib_goods.php');


/*------------------------------------------------------ */
//--余额，积分，红包对账
/*------------------------------------------------------ */

if ($_REQUEST['act'] == 'list')
{
    /* 检查权限 */
    admin_priv('point_bouns');
    
	/*类型 默认为1 余额 2积分 3红包*/
	$type = isset($_REQUEST['type']) ? intval($_REQUEST['type']) : 1;
	
    /* 模板赋值 */
    $smarty->assign('ur_here', "余额，积分，红包对账");

    $smarty->assign('full_page',        1);

	/* 分页大小 */
    $filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);
	
	 if (isset($_REQUEST['page_size']) && intval($_REQUEST['page_size']) > 0)
	{
		$filter['page_size'] = intval($_REQUEST['page_size']);
	}
	elseif (isset($_COOKIE['ECSCP']['page_size']) && intval($_COOKIE['ECSCP']['page_size']) > 0)
	{
		$filter['page_size'] = intval($_COOKIE['ECSCP']['page_size']);
	}
	else
	{
		$filter['page_size'] = 15;
	}

	//var_dump($order_list);
    $smarty->assign('order_list',   "");
    $smarty->assign('filter',       $filter);
    $smarty->assign('record_count', 0);
    $smarty->assign('page_count',   1);
    $smarty->assign('sort_order_time', '<img src="images/sort_desc.gif">');
    
	$smarty->assign('type',   $type);
	
    /* 显示模板 */
    assign_query_info();
    $smarty->display('point_bouns.htm');
}

/*查询数据*/
if ($_REQUEST['act'] == 'query')
{
	
	/* 检查权限 */
    admin_priv('point_bouns');

	/*类型 默认为1 余额 2积分 3红包*/
	$type = isset($_REQUEST['type']) ? intval($_REQUEST['type']) : 1;
	
	if($type == '1'){
		
		$order_list = accounts_list1();

	}elseif($type == '2'){
		
		$order_list = accounts_list2();
		
	}elseif($type == '3'){
		
		$order_list = accounts_list3();
	}
	

    $smarty->assign('list',   $order_list['list']);
    $smarty->assign('filter',       $order_list['filter']);
    $smarty->assign('record_count', $order_list['record_count']);
    $smarty->assign('page_count',   $order_list['page_count']);
	$smarty->assign('type',   $type);
    $sort_flag  = sort_flag($order_list['filter']);
    $smarty->assign($sort_flag['tag'], $sort_flag['img']);
    make_json_result($smarty->fetch('point_bouns.htm'), '', array('filter' => $order_list['filter'], 'page_count' => $order_list['page_count']));
	
}

/*查询数据列表*/
/*$type 类型 1余额 2积分 3红包*/
function accounts_list1(){
	
	/*查询数据*/
	$filter['type'] = 1;
	$filter['start_time'] = empty($_REQUEST['start_time']) ? '' : (strpos($_REQUEST['start_time'], '-') > 0 ?  local_strtotime($_REQUEST['start_time']) : $_REQUEST['start_time']);
		
    $filter['end_time'] = empty($_REQUEST['end_time']) ? '' : (strpos($_REQUEST['end_time'], '-') > 0 ?  local_strtotime($_REQUEST['end_time']) : $_REQUEST['end_time']);

	/*前值时间*/
	$a_start_time = $filter['start_time']+16*3600;

	/*后值时间*/
	$a_end_time = $filter['end_time']+16*3600;
	

	//$sql3 = "SELECT * FROM (($sql) left join ($sql2) on a.user_id = b.user_id ) left join ecs_account_log AS c WHERE c.user_money <> 0 and c.change_time >= '".$a_start_time."' and c.change_time <= '".$a_end_time."' group by c.user_id ";
	
	//$row = $GLOBALS['db']->getAll($sql3);
	
	//$sql = "SELECT GROUP_CONCAT( CAST(  `user_id` AS CHAR ) ) AS uid FROM ecs_account_log  WHERE user_money <> 0 and change_time >= '".$a_start_time."' and change_time <= '".$a_end_time."' group by user_id ";
	
	$sql = "SELECT distinct user_id FROM ecs_account_log  WHERE user_money <> 0 and change_time >= '".$a_start_time."' and change_time <= '".$a_end_time."' ";
	
	$row = $GLOBALS['db']->getAll($sql);
	
	/*用户ID 逗号隔开*/
	$new_row = '';
	if(!empty($row)){
		
		foreach($row as $key=>$value){
			
			if(!empty($value['user_id'])){
				$new_row .= $value['user_id'].",";
			}
		
		}
		
		//去除最后一个逗号
		$new_row = substr($new_row, 0, -1);
		
	}

	
	// echo $new_row;
	// echo "<pre>";
	// print_r($row);
	// echo "<pre>";
	// die;
	
	if(!empty($new_row)){
	
		/* 分页大小 */
		$filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);
		
		 if (isset($_REQUEST['page_size']) && intval($_REQUEST['page_size']) > 0)
		{
			$filter['page_size'] = intval($_REQUEST['page_size']);
		}
		elseif (isset($_COOKIE['ECSCP']['page_size']) && intval($_COOKIE['ECSCP']['page_size']) > 0)
		{
			$filter['page_size'] = intval($_COOKIE['ECSCP']['page_size']);
		}
		else
		{
			$filter['page_size'] = 15;
		}
		
		$sqlc = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) ";
		
		$filter['record_count']   = $GLOBALS['db']->getOne($sqlc);
		$filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / $filter['page_size']) : 1;
		
		
		/*根据users表查询数据*/
		$sqlu = "SELECT user_id, user_name FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) " . " LIMIT " . ($filter['page'] - 1) * $filter['page_size'] . ",$filter[page_size]";

		$lists = $GLOBALS['db']->getAll($sqlu);
		
		/*详细数据*/
		$sql2 = "SELECT sum(b.user_money) as b.h_money FROM ecs_account_log AS b WHERE b.user_money <> 0 and b.change_time <= '".$a_end_time."' group by b.user_id ";
		
		foreach ($lists as $key => $item) {
			
			/*前值余额*/
			$q_money = $GLOBALS['db']->getOne("SELECT sum(a.user_money) as q_money FROM ecs_account_log AS a WHERE a.user_money <> 0 and a.change_time <= '".$a_start_time."' and a.user_id = '".$item['user_id']."' ");
			
			if(empty($q_money)){
				$lists[$key]['q_money'] = 0;
			}else{
				$lists[$key]['q_money'] = $q_money;
			}
			
			
			/*后值余额*/
			$h_money = $GLOBALS['db']->getOne("SELECT sum(b.user_money) as h_money FROM ecs_account_log AS b WHERE b.user_money <> 0 and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($h_money)){
				$lists[$key]['h_money'] = 0;
			}else{
				$lists[$key]['h_money'] = $h_money;
			}

			/*充值金额  不仅仅查询 b.change_type = 0 */
			$c_money = $GLOBALS['db']->getOne("SELECT sum(b.user_money) as c_money FROM ecs_account_log AS b WHERE b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_money > 0  and b.user_id = '".$item['user_id']."' ");
			
			if(empty($c_money)){
				$lists[$key]['c_money'] = 0;
			}else{
				$lists[$key]['c_money'] = $c_money;
			}
			
			
			/*消费金额*/
			$t_money = $GLOBALS['db']->getOne("SELECT sum(b.user_money) as t_money FROM ecs_account_log AS b WHERE b.user_money < 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($t_money)){
				$lists[$key]['t_money'] = 0;
			}else{
				$lists[$key]['t_money'] = $t_money;
			}

			/*详细数据*/
			$list_money = $GLOBALS['db']->getAll("SELECT * FROM ecs_account_log AS b WHERE b.user_money <> 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");	
			
			
			foreach($list_money as $k=>$v){
				
				$list_money[$k]['time'] = local_date('Y-m-d H:i:s',$v['change_time']);
			}
			
			$lists[$key]['list_money'] = $list_money;
			
			
		}
	}
    $arr = array('list' => $lists, 'filter' => $filter, 'page_count' => $filter['page_count'], 'record_count' => $filter['record_count']);

    return $arr;
	
	
}

function accounts_list2(){
	
	/*查询数据*/
	$filter['type'] = 2;
	$filter['start_time'] = empty($_REQUEST['start_time']) ? '' : (strpos($_REQUEST['start_time'], '-') > 0 ?  local_strtotime($_REQUEST['start_time']) : $_REQUEST['start_time']);
		
    $filter['end_time'] = empty($_REQUEST['end_time']) ? '' : (strpos($_REQUEST['end_time'], '-') > 0 ?  local_strtotime($_REQUEST['end_time']) : $_REQUEST['end_time']);

	/*前值时间*/
	$a_start_time = $filter['start_time']+16*3600;

	/*后值时间*/
	$a_end_time = $filter['end_time']+16*3600;
	

	//$sql3 = "SELECT * FROM (($sql) left join ($sql2) on a.user_id = b.user_id ) left join ecs_account_log AS c WHERE c.user_money <> 0 and c.change_time >= '".$a_start_time."' and c.change_time <= '".$a_end_time."' group by c.user_id ";
	
	//$row = $GLOBALS['db']->getAll($sql3);
	
	//$sql = "SELECT GROUP_CONCAT( CAST(  `user_id` AS CHAR ) ) AS uid FROM ecs_account_log  WHERE user_money <> 0 and change_time >= '".$a_start_time."' and change_time <= '".$a_end_time."' group by user_id ";
	
	$sql = "SELECT distinct user_id FROM ecs_account_log  WHERE pay_points <> 0 and change_time >= '".$a_start_time."' and change_time <= '".$a_end_time."' ";
	
	$row = $GLOBALS['db']->getAll($sql);
	
	/*用户ID 逗号隔开*/
	$new_row = '';
	if(!empty($row)){
		
		foreach($row as $key=>$value){
			
			if(!empty($value['user_id'])){
				$new_row .= $value['user_id'].",";
			}
		
		}
		
		//去除最后一个逗号
		$new_row = substr($new_row, 0, -1);
		
	}

	
	// echo $new_row;
	// echo "<pre>";
	// print_r($row);
	// echo "<pre>";
	// die;
	if(!empty($new_row)){
		
		/* 分页大小 */
		$filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);
		
		 if (isset($_REQUEST['page_size']) && intval($_REQUEST['page_size']) > 0)
		{
			$filter['page_size'] = intval($_REQUEST['page_size']);
		}
		elseif (isset($_COOKIE['ECSCP']['page_size']) && intval($_COOKIE['ECSCP']['page_size']) > 0)
		{
			$filter['page_size'] = intval($_COOKIE['ECSCP']['page_size']);
		}
		else
		{
			$filter['page_size'] = 15;
		}
		
		$sqlc = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) ";
		
		$filter['record_count']   = $GLOBALS['db']->getOne($sqlc);
		$filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / $filter['page_size']) : 1;
		
		
		/*根据users表查询数据*/
		$sqlu = "SELECT user_id, user_name FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) " . " LIMIT " . ($filter['page'] - 1) * $filter['page_size'] . ",$filter[page_size]";

		$lists = $GLOBALS['db']->getAll($sqlu);
		
		foreach ($lists as $key => $item) {
			
			/*前值余额*/
			$q_money = $GLOBALS['db']->getOne("SELECT sum(a.pay_points) as q_money FROM ecs_account_log AS a WHERE a.pay_points <> 0 and a.change_time <= '".$a_start_time."' and a.user_id = '".$item['user_id']."' ");
			
			if(empty($q_money)){
				$lists[$key]['q_money'] = 0;
			}else{
				$lists[$key]['q_money'] = $q_money;
			}
			
			
			/*后值余额*/
			$h_money = $GLOBALS['db']->getOne("SELECT sum(b.pay_points) as h_money FROM ecs_account_log AS b WHERE b.pay_points <> 0 and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($h_money)){
				$lists[$key]['h_money'] = 0;
			}else{
				$lists[$key]['h_money'] = $h_money;
			}

			/*充值金额  不仅仅查询 b.change_type = 0 */
			$c_money = $GLOBALS['db']->getOne("SELECT sum(b.pay_points) as c_money FROM ecs_account_log AS b WHERE b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.pay_points > 0  and b.user_id = '".$item['user_id']."' ");
			
			if(empty($c_money)){
				$lists[$key]['c_money'] = 0;
			}else{
				$lists[$key]['c_money'] = $c_money;
			}
			
			
			/*消费金额*/
			$t_money = $GLOBALS['db']->getOne("SELECT sum(b.pay_points) as t_money FROM ecs_account_log AS b WHERE b.pay_points < 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($t_money)){
				$lists[$key]['t_money'] = 0;
			}else{
				$lists[$key]['t_money'] = $t_money;
			}

			/*详细数据*/
			$list_money = $GLOBALS['db']->getAll("SELECT * FROM ecs_account_log AS b WHERE b.pay_points <> 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");	
			
			
			foreach($list_money as $k=>$v){
				
				$list_money[$k]['time'] = local_date('Y-m-d H:i:s',$v['change_time']);
			}
			
			$lists[$key]['list_money'] = $list_money;
			
			
		}
	}
    $arr = array('list' => $lists, 'filter' => $filter, 'page_count' => $filter['page_count'], 'record_count' => $filter['record_count']);
		
    return $arr;
	
	
}


function accounts_list3(){
	
	/*查询数据*/
	$filter['type'] = 3;
	$filter['start_time'] = empty($_REQUEST['start_time']) ? '' : (strpos($_REQUEST['start_time'], '-') > 0 ?  local_strtotime($_REQUEST['start_time']) : $_REQUEST['start_time']);
		
    $filter['end_time'] = empty($_REQUEST['end_time']) ? '' : (strpos($_REQUEST['end_time'], '-') > 0 ?  local_strtotime($_REQUEST['end_time']) : $_REQUEST['end_time']);

	/*前值时间*/
	$a_start_time = $filter['start_time']+16*3600;

	/*后值时间*/
	$a_end_time = $filter['end_time']+16*3600;
	
	$sql = "SELECT distinct user_id FROM ecs_user_bonus WHERE used_time != 0 and used_time >= '".$a_start_time."' and used_time <= '".$a_end_time."' ";
	
	$row = $GLOBALS['db']->getAll($sql);
	
	/*用户ID 逗号隔开*/
	$new_row = '';
	if(!empty($row)){
		
		foreach($row as $key=>$value){
			
			if(!empty($value['user_id'])){
				$new_row .= $value['user_id'].",";
			}
		
		}
		
		//去除最后一个逗号
		$new_row = substr($new_row, 0, -1);
		
	}

	
	// echo $new_row;
	// echo "<pre>";
	// print_r($row);
	// echo "<pre>";
	// die;
	
	if(!empty($new_row)){
		
		/* 分页大小 */
		$filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);
		
		 if (isset($_REQUEST['page_size']) && intval($_REQUEST['page_size']) > 0)
		{
			$filter['page_size'] = intval($_REQUEST['page_size']);
		}
		elseif (isset($_COOKIE['ECSCP']['page_size']) && intval($_COOKIE['ECSCP']['page_size']) > 0)
		{
			$filter['page_size'] = intval($_COOKIE['ECSCP']['page_size']);
		}
		else
		{
			$filter['page_size'] = 15;
		}
		
		$sqlc = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) ";
		
		$filter['record_count']   = $GLOBALS['db']->getOne($sqlc);
		$filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / $filter['page_size']) : 1;
		
		
		/*根据users表查询数据*/
		$sqlu = "SELECT user_id, user_name FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) " . " LIMIT " . ($filter['page'] - 1) * $filter['page_size'] . ",$filter[page_size]";

		$lists = $GLOBALS['db']->getAll($sqlu);
		
		foreach ($lists as $key => $item) {
			
			/*红包总抵扣值*/
			$bonus_list = $GLOBALS['db']->getAll("SELECT * FROM ecs_user_bonus AS a WHERE a.used_time != 0 and a.used_time >= '".$a_start_time."'  and a.used_time <= '".$a_end_time."' and a.user_id = '".$item['user_id']."' ");
			
			$type_money = 0;
			
			if(!empty($bonus_list)){
				
				foreach($bonus_list as $k=>$value){
					
					$type_money += $GLOBALS['db']->getOne("SELECT type_money FROM ecs_bonus_type  WHERE  type_id = '".$value['bonus_type_id']."' ");	
					
					$bonus_list[$k]['time'] = local_date('Y-m-d H:i:s',$value['used_time']);
				}
			}
			
			$lists[$key]['type_money'] = $type_money;
			
			/*详细数据*/
			$lists[$key]['bonus_list'] = $bonus_list;
			
			
		}
    }
    $arr = array('list' => $lists, 'filter' => $filter, 'page_count' => $filter['page_count'], 'record_count' => $filter['record_count']);
		
    return $arr;
	
	
}
